﻿using System;
using System.Windows.Forms;
using System.Data;
using System.Collections.Generic;

namespace iPower
{
    class iPowerDBQuery
    {
        // Return DataRow for ComPort values for the selected Site and Meter
        public Dictionary<string,string> getSelectedDBComPort(string strSiteSelected, string strMeterSelected)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            Dictionary<string,string> dicComport = new Dictionary<string,string>();
            DataTable dtComPort;
            String sqlComPort;

            sqlComPort = "select distinct METERS.PORT \"PORT\", METERS.SLAVE \"SLAVE\", METERS.BAUD \"BAUD\", METERS.DATABITS \"DATABITS\", ";
            sqlComPort += "METERS.PARITY \"PARITY\", METERS.STOPBITS \"STOPBITS\" ";
            sqlComPort += "from METERS, SITES ";
            sqlComPort += "where METERS.SITES_ID = SITES.ID ";
            sqlComPort += String.Format("and SITES.NAMES=\"{0}\" ", strSiteSelected);
            sqlComPort += String.Format("and METERS.NAMES=\"{0}\"", strMeterSelected);

            try
            {
                dtComPort = db.GetDataTable(sqlComPort);

                foreach (DataRow dr in dtComPort.Rows)
                {
                    dicComport.Add("PORT", dr["PORT"].ToString());
                    dicComport.Add("SLAVE", dr["SLAVE"].ToString());
                    dicComport.Add("BAUD", dr["BAUD"].ToString());
                    dicComport.Add("DATABITS", dr["DATABITS"].ToString());
                    dicComport.Add("PARITY", dr["PARITY"].ToString());
                    dicComport.Add("STOPBITS", dr["STOPBITS"].ToString());
                } //foreach

            } //try
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
            } //catch

            return dicComport;
        } //getSelectedDBSitesMeters


        // Return Dictionary of Meter Register values for the selected Site and Meter
        public Dictionary<string, string> getSelectedDBMeterReg(string strSiteSelected, string strMeterSelected)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            Dictionary<string, string> dicMeterReg = new Dictionary<string, string>();
            DataTable dtMeterReg;
            String sqlMeterReg;

            sqlMeterReg = "select distinct METERTYPES.V1LL \"V1LL\", METERTYPES.V2LL \"V2LL\", METERTYPES.V3LL \"V3LL\", ";
            sqlMeterReg += "METERTYPES.V1LN \"V1LN\", METERTYPES.V2LN \"V2LN\", METERTYPES.V3LN \"V3LN\", ";
            sqlMeterReg += "METERTYPES.I1 \"I1\", METERTYPES.I2 \"I2\", METERTYPES.I3 \"I3\", METERTYPES.NI \"NI\", ";
            sqlMeterReg += "METERTYPES.FREQ \"FREQ\", METERTYPES.PF \"PF\", ";
            sqlMeterReg += "METERTYPES.KW1 \"KW1\", METERTYPES.KW2 \"KW2\", METERTYPES.KW3 \"KW3\", ";
            sqlMeterReg += "METERTYPES.KVA1 \"KVA1\", METERTYPES.KVA2 \"KVA2\", METERTYPES.KVA3 \"KVA3\", ";
            sqlMeterReg += "METERTYPES.KWH \"KWH\", METERTYPES.KVAH \"KVAH\", METERTYPES.KVARH \"KVARH\" ";
            sqlMeterReg += "from METERS, METERTYPES, SITES ";
            sqlMeterReg += "where METERS.METERTYPES_ID = METERTYPES.ID ";
            sqlMeterReg += "and METERS.SITES_ID = SITES.ID ";
            sqlMeterReg += String.Format("and SITES.NAMES=\"{0}\" ", strSiteSelected);
            sqlMeterReg += String.Format("and METERS.NAMES=\"{0}\"", strMeterSelected);

            try
            {
                dtMeterReg = db.GetDataTable(sqlMeterReg);

                foreach(DataRow dr in dtMeterReg.Rows)
                {
                    dicMeterReg.Add("V1LL", dr["V1LL"].ToString());
                    dicMeterReg.Add("V2LL", dr["V2LL"].ToString());
                    dicMeterReg.Add("V3LL", dr["V3LL"].ToString());
                    dicMeterReg.Add("V1LN", dr["V1LN"].ToString());
                    dicMeterReg.Add("V2LN", dr["V1LN"].ToString());
                    dicMeterReg.Add("V3LN", dr["V1LN"].ToString());
                    dicMeterReg.Add("I1", dr["I1"].ToString());
                    dicMeterReg.Add("I2", dr["I2"].ToString());
                    dicMeterReg.Add("I3", dr["I3"].ToString());
                    dicMeterReg.Add("NI", dr["NI"].ToString());
                    dicMeterReg.Add("FREQ", dr["FREQ"].ToString());
                    dicMeterReg.Add("PF", dr["PF"].ToString());
                    dicMeterReg.Add("KW1", dr["KW1"].ToString());
                    dicMeterReg.Add("KW2", dr["KW2"].ToString());
                    dicMeterReg.Add("KW3", dr["KW3"].ToString());
                    dicMeterReg.Add("KVA1", dr["KVA1"].ToString());
                    dicMeterReg.Add("KVA2", dr["KVA2"].ToString());
                    dicMeterReg.Add("KVA3", dr["KVA3"].ToString());
                    dicMeterReg.Add("KWH", dr["KWH"].ToString());
                    dicMeterReg.Add("KVAH", dr["KVAH"].ToString());
                    dicMeterReg.Add("KVARH", dr["KVARH"].ToString());
                }//foreach

            } //try
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
            } //catch

            return dicMeterReg;
        } //getSelectedDBMeterReg

        // Return Dictionary of Meter Data Type values for the selected Site and Meter
        public Dictionary<string, string> getSelectedDBMeterDatatype(string strSiteSelected, string strMeterSelected)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            Dictionary<string, string> dicDatatype = new Dictionary<string, string>();
            DataTable dtMeterDatatype;
            String sqlMeterDatatype;

            sqlMeterDatatype = "select distinct DATATYPES.V1LL \"V1LL\", DATATYPES.V2LL \"V2LL\", DATATYPES.V3LL \"V3LL\", ";
            sqlMeterDatatype += "DATATYPES.V1LN \"V1LN\", DATATYPES.V2LN \"V2LN\", DATATYPES.V3LN \"V3LN\", ";
            sqlMeterDatatype += "DATATYPES.I1 \"I1\", DATATYPES.I2 \"I2\", DATATYPES.I3 \"I3\", DATATYPES.NI \"NI\", ";
            sqlMeterDatatype += "DATATYPES.FREQ \"FREQ\", DATATYPES.PF \"PF\", ";
            sqlMeterDatatype += "DATATYPES.KW1 \"KW1\", DATATYPES.KW2 \"KW2\", DATATYPES.KW3 \"KW3\", ";
            sqlMeterDatatype += "DATATYPES.KVA1 \"KVA1\", DATATYPES.KVA2 \"KVA2\", DATATYPES.KVA3 \"KVA3\", ";
            sqlMeterDatatype += "DATATYPES.KWH \"KWH\", DATATYPES.KVAH \"KVAH\", DATATYPES.KVARH \"KVARH\" ";
            sqlMeterDatatype += "from METERS, METERTYPES, DATATYPES, SITES ";
            sqlMeterDatatype += "where METERS.METERTYPES_ID = METERTYPES.ID ";
            sqlMeterDatatype += "and METERS.SITES_ID = SITES.ID ";
            sqlMeterDatatype += "and METERTYPES.DATATYPES_ID = DATATYPES.ID ";
            sqlMeterDatatype += String.Format("and SITES.NAMES=\"{0}\" ", strSiteSelected);
            sqlMeterDatatype += String.Format("and METERS.NAMES=\"{0}\"", strMeterSelected);

            try
            {
                dtMeterDatatype = db.GetDataTable(sqlMeterDatatype);

                foreach (DataRow dr in dtMeterDatatype.Rows)
                {
                    dicDatatype.Add("V1LL", dr["V1LL"].ToString());
                    dicDatatype.Add("V2LL", dr["V2LL"].ToString());
                    dicDatatype.Add("V3LL", dr["V3LL"].ToString());
                    dicDatatype.Add("V1LN", dr["V1LN"].ToString());
                    dicDatatype.Add("V2LN", dr["V1LN"].ToString());
                    dicDatatype.Add("V3LN", dr["V1LN"].ToString());
                    dicDatatype.Add("I1", dr["I1"].ToString());
                    dicDatatype.Add("I2", dr["I2"].ToString());
                    dicDatatype.Add("I3", dr["I3"].ToString());
                    dicDatatype.Add("NI", dr["NI"].ToString());
                    dicDatatype.Add("FREQ", dr["FREQ"].ToString());
                    dicDatatype.Add("PF", dr["PF"].ToString());
                    dicDatatype.Add("KW1", dr["KW1"].ToString());
                    dicDatatype.Add("KW2", dr["KW2"].ToString());
                    dicDatatype.Add("KW3", dr["KW3"].ToString());
                    dicDatatype.Add("KVA1", dr["KVA1"].ToString());
                    dicDatatype.Add("KVA2", dr["KVA2"].ToString());
                    dicDatatype.Add("KVA3", dr["KVA3"].ToString());
                    dicDatatype.Add("KWH", dr["KWH"].ToString());
                    dicDatatype.Add("KVAH", dr["KVAH"].ToString());
                    dicDatatype.Add("KVARH", dr["KVARH"].ToString());
                }//foreach

            } //try
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
            } //catch

            return dicDatatype;
        } //getSelectedDBMeterDataType


        // Return Dictionary of Scale Type values for the selected Site and Meter
        public Dictionary<string, string> getSelectedDBMeterScale(string strSiteSelected, string strMeterSelected)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            Dictionary<string, string> dicScaletype = new Dictionary<string, string>();
            DataTable dtMeterScaletype;
            String sqlMeterScaletype;

            sqlMeterScaletype = "select distinct SCALE.V \"V\", SCALE.I \"I\", SCALE.E \"E\", SCALE.W \"W\", SCALE.Hz \"Hz\" ";
            sqlMeterScaletype += "from METERS, METERTYPES, DATATYPES, SITES, SCALE ";
            sqlMeterScaletype += "where METERS.METERTYPES_ID = METERTYPES.ID ";
            sqlMeterScaletype += "and METERS.SITES_ID = SITES.ID ";
            sqlMeterScaletype += "and METERTYPES.DATATYPES_ID = DATATYPES.ID ";
            sqlMeterScaletype += "and DATATYPES.SCALE_ID = SCALE.ID ";
            sqlMeterScaletype += String.Format("and SITES.NAMES=\"{0}\" ", strSiteSelected);
            sqlMeterScaletype += String.Format("and METERS.NAMES=\"{0}\"", strMeterSelected);

            try
            {
                dtMeterScaletype = db.GetDataTable(sqlMeterScaletype);

                foreach (DataRow dr in dtMeterScaletype.Rows)
                {
                    dicScaletype.Add("V", dr["V"].ToString());
                    dicScaletype.Add("I", dr["I"].ToString());
                    dicScaletype.Add("E", dr["E"].ToString());
                    dicScaletype.Add("W", dr["W"].ToString());
                    dicScaletype.Add("Hz", dr["Hz"].ToString());
                }//foreach

            } //try
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
            } //catch

            return dicScaletype;
        } //getSelectedDBMeterDataType

        // Returns a List with all the Sites that have Meters Assigned
        public List<string> getSites()
        {
            SQLiteDatabase db = new SQLiteDatabase();
            List<string> listSites = new List<string>();
            DataTable dtSites;
            String sqlSites;

            sqlSites = "select distinct SITES.NAMES \"NAMES\" ";
            sqlSites += "from SITES, METERS ";
            sqlSites += "where METERS.SITES_ID = SITES.ID";

            try
            {
                dtSites = db.GetDataTable(sqlSites);

                foreach (DataRow dr in dtSites.Rows)
                {
                    listSites.Add(dr["NAMES"].ToString());
                }//foreach

            } //try
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
            } //catch

            return listSites;
        }//getSites

        // Returns a List with all the Meters for a specific Site and QueryTime
        public List<string> getMeters(string SiteName, string SampleTime)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            List<string> listMeters = new List<string>();
            DataTable dtMeters;
            String sqlMeters;

            sqlMeters = "select distinct METERS.NAMES \"NAMES\" ";
            sqlMeters += "from SITES, METERS ";
            sqlMeters += "where METERS.SITES_ID = SITES.ID ";            
            sqlMeters += String.Format("and SITES.NAMES=\"{0}\" ", SiteName);
            sqlMeters += String.Format("and METERS.QUERYTIME=\"{0}\"", SampleTime);

            try
            {
                dtMeters = db.GetDataTable(sqlMeters);

                foreach (DataRow dr in dtMeters.Rows)
                {
                    listMeters.Add(dr["NAMES"].ToString());
                }//foreach

            } //try
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
            } //catch

            return listMeters;
        }//getMeters

        // Returns a List with all the Meters for a specific Site
        public List<string> getMeters(string SiteName)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            List<string> listMeters = new List<string>();
            DataTable dtMeters;
            String sqlMeters;

            sqlMeters = "select distinct METERS.NAMES \"NAMES\" ";
            sqlMeters += "from SITES, METERS ";
            sqlMeters += "where METERS.SITES_ID = SITES.ID ";
            sqlMeters += String.Format("and SITES.NAMES=\"{0}\"", SiteName);            

            try
            {
                dtMeters = db.GetDataTable(sqlMeters);

                foreach (DataRow dr in dtMeters.Rows)
                {
                    listMeters.Add(dr["NAMES"].ToString());
                }//foreach

            } //try
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
            } //catch

            return listMeters;
        }//getMeters

        public void insDatalog(Dictionary<string, double> dicDatalog, string strMeter, string strDatalogDB)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            Dictionary<String, String> dicData = new Dictionary<String, String>();
            String sqlMeterID;

            //SQL Query to Get Meter ID for Meter String
            sqlMeterID = "select distinct ID ";
            sqlMeterID += "from METERS ";
            sqlMeterID += String.Format("where NAMES=\"{0}\"", strMeter);

            // Datetime from C#
            dicData.Add("TIMESTAMP", String.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now));

            // Get Meter ID from SQL Query
            dicData.Add("METERS_ID", db.ExecuteScalar(sqlMeterID));
            
            foreach (KeyValuePair<string, double> pair in dicDatalog)
            {
                // Copy and Parse Values
                dicData.Add(pair.Key,pair.Value.ToString());
            }//foreach 

            // Try to INSERT to DB
            try
            {
                db.Insert(strDatalogDB, dicData);
            } //try
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
            } //catch

        } //insDatalog

    } //class
} //namespace
